package common.main.cnkiPatent;

import java.sql.*;
import java.util.List;

public class CnkiPatentSaveDataToSql {

    private static Connection conn = null;
    private static Statement stmt = null;

    private static void connectDatabase() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String databaseHost = CnkiPatentConfig.mysqlHost;//
        String databaseName = CnkiPatentConfig.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
        String userName = CnkiPatentConfig.mysqlUserName;// MySQL默认的root账户名
        String password = CnkiPatentConfig.mysqlPassword;// 默认的root账户密码为空
        String url ="jdbc:mysql://"+databaseHost+"/"
                +databaseName+"?user="+userName+"&password="+password+"&useUnicode=true&characterEncoding=UTF-8";
        if(conn == null || conn.isClosed()){
            conn = DriverManager.getConnection(url);
        }
        if(stmt == null || stmt.isClosed()){
            stmt = conn.createStatement();
        }
    }

    public static String getAllPublicNum(){
        try {
            connectDatabase();
            String sql = "select publicNum from cnki_patent";

            ResultSet rs = stmt.executeQuery(sql);
            StringBuilder sb = new StringBuilder();
            while (rs.next()){
                sb.append(rs.getString("publicNum") + ",");
            }
            return sb.toString();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "";
    }

    public static void saveCnkiPaperToSql(List<CnkiPatentData> patentList) {
        try{
            connectDatabase();
            String sql_model = "INSERT INTO cnki_patent (patentName,requestNum,requestDate,publicNum,publicDate,proposer,originator," +
                    "patentAgency,proxy,nationalCode,brief,principalClaim,pageNum,mainClassificationNum,patentClassificationNum)" +
                    "values ('<patentName>','<requestNum>','<requestDate>','<publicNum>','<publicDate>','<proposer>','<originator>'," +
                    "'<patentAgency>','<proxy>','<nationalCode>','<brief>','<principalClaim>','<pageNum>','<mainClassificationNum>','<patentClassificationNum>')";
            for(int i = 0;i < patentList.size();i++){
                CnkiPatentData patent = patentList.get(i);

                String sql = sql_model.replace("<patentName>",patent.getPatentName())
                        .replace("<requestNum>",patent.getRequestNum())
                        .replace("<requestDate>",patent.getRequestDate())
                        .replace("<publicNum>",patent.getPublicNum())
                        .replace("<publicDate>",patent.getPublicDate())
                        .replace("<proposer>",patent.getProposer() + "")
                        .replace("<originator>",patent.getOriginator())
                        .replace("<patentAgency>",patent.getPatentAgency())
                        .replace("<proxy>",patent.getProxy() + "")
                        .replace("<nationalCode>",patent.getNationalCode() + "")
                        .replace("<brief>",patent.getBrief() + "")
                        .replace("<principalClaim>",patent.getPrincipalClaim())
                        .replace("<pageNum>",patent.getPageNum())
                        .replace("<mainClassificationNum>",patent.getMainClassificationNum())
                        .replace("<patentClassificationNum>",patent.getPatentClassificationNum());

                try{
                    stmt.executeUpdate(sql);
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            conn.close();
            stmt.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
